package com.eam.handler.facilities;

import com.basis.handler.service.DataImportInterface;
import com.basis.utils.CodeGenerateRuleUtil;
import com.eam.data.*;
import com.mes.data.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.codehaus.jettison.json.JSONObject;
import org.openbravo.dal.service.OBDal;
import org.openbravo.database.SessionInfo;
import org.openbravo.model.ad.access.User;
import org.openbravo.model.common.enterprise.Organization;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@SuppressWarnings("unchecked")
public class MaintainPlanImportService extends DataImportInterface {
	protected static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//处理时间格式
	protected String tableName = null;
	protected String clientId = null;
	protected String orgId = null;
	protected String userId = null;
	private boolean flag = true;
	private String maintainid = "";
	private String itemid = "";

	public void setEmesDataImport(EmesDataImport dataImport) {
	}

	public JSONObject importData(InputStream is, String fileName) throws Exception {
		JSONObject result = new JSONObject();
		try {
			Workbook wb = null;
			if (fileName.endsWith(".xls")) {
				wb = new HSSFWorkbook(is);
			} else if (fileName.endsWith(".xlsx")) {
				wb = new XSSFWorkbook(is);
			}
			if (wb != null) {
				result = this.readExcel(wb);
				if (!result.getBoolean("result")) {
					OBDal.getInstance().rollbackAndClose();
					return result;
				} else {
					OBDal.getInstance().commitAndClose();
					result.put("result", true);
					result.put("msg", "导入成功");
					return result;
				}
			} else {
				result.put("msg", "文件格式错误，至于许EXCEL的导入");
				return result;
			}
		} catch (Exception e) {
			e.printStackTrace();
			OBDal.getInstance().rollbackAndClose();
			if (e.getClass().getName().contains("ConstraintViolationException")) {
				result.put("result", false);
				result.put("msg", "文件数据有问题，请仔细检查。");
			}
		} finally {
			is.close();
		}
		return result;
	}

	private JSONObject readExcel(Workbook wb) throws Exception {
		String meg = "", errorMsg;
		int number = wb.getNumberOfSheets();
		JSONObject result = new JSONObject();

		result.put("result", true);
		if (number <= 0) {
			result.put("result", false);
			result.put("msg", "文件格式错误。请仔细检查");
			return result;
		} else {
			// 读取第一页
			Sheet sheet = wb.getSheetAt(0);
			int lastRowNum = sheet.getLastRowNum();
			for (int i = 2; i <= lastRowNum; i++) {
				Row row = sheet.getRow(i);
				errorMsg = this.checkData(row);
				if (errorMsg != null && !"".equals(errorMsg)) {
					meg = errorMsg;
				}
			}
			if (!"".equals(meg)) {
				result.put("result", false);
				result.put("msg", meg);
			}
			return result;
		}
	}

	/**
	 * 开始存储记录
	 *
	 * @param row
	 * @return
	 * @throws Exception
	 */
	private String checkData(Row row) throws Exception {
		String meg = "";
		try {
			String Organizations = (String) readValue(row.getCell(0)); //组织机构
			String planName = (String) readValue(row.getCell(1)); //保养计划名称
			String program = (String) readValue(row.getCell(2)); //设备保养项目
			String code = (String) readValue(row.getCell(3));  //设备编码
			Date planstatetime = (Date) readValue(row.getCell(4)); //计划开始时间
			Date planendtime = (Date) readValue(row.getCell(5)); //计划结束时间
			Double sumtime = (Double) readValue(row.getCell(6)); //计划总时长
			String username = (String) readValue(row.getCell(7));//保养项目计划负责人
			String groupusers = (String) readValue(row.getCell(8)); //设备保养项目参与成员
			String plantype = (String) readValue(row.getCell(9)); //保养计划类型（周、月、季度、年）
			Double Advanceday = (Double) readValue(row.getCell(10)); //提前预警时间

			BigDecimal Advancedays = new BigDecimal(Advanceday);

			String sumtimes = sumtime.toString();

			String[] users = groupusers.split(",");
			int number = users.length;

			String status = "CREATE";    //创建状态
			Boolean effective = true;            //有效
			String types = "MAINTAIN";        //类型
			if (plantype.equals("周")) {
				plantype = "week";
			} else if (plantype.equals("月")) {
				plantype = "month";
			} else if (plantype.equals("季度")) {
				plantype = "quarter";
			} else if (plantype.equals("年")) {
				plantype = "year";
			} else {
				meg = "【第" + (row.getRowNum() + 1) + "行计划类型错误,只有周、月、季度、年计划！！";
			}

			//---------------------------------------------------------------------------------存保养计划------------------------------------------------------------------------------------------

			String plancode = CodeGenerateRuleUtil.getCode("Eam_Maintain_Plan", "sparecode");    //保养计划编码

			String userid = SessionInfo.getUserId(); //获取当前登录人的ad_user_id设为导入人的id
			String wql = "select * from ad_user where ad_user_id = ? ";

			User userids = (User) OBDal.getInstance().getSession().createSQLQuery(wql).addEntity(User.ENTITY_NAME).setString(0, userid).uniqueResult();    //责任人ID

			String tql = "select * from ad_user where name = ? ";

			User usernames = (User) OBDal.getInstance().getSession().createSQLQuery(tql).addEntity(User.ENTITY_NAME).setString(0, username).uniqueResult();    //查询计划负责人是否存在

			String uql = "select * from ad_org where name = ?";

			Organization orgIds = (Organization) OBDal.getInstance().getSession().createSQLQuery(uql).addEntity(Organization.ENTITY_NAME).setString(0, Organizations).uniqueResult(); //查询组织机构的ID

			String sql = "select * " +
					" from Eam_Maintain byxm " +
					" left join Eam_Facilities_Maintain sbxq on byxm.Eam_Maintain_id = sbxq.Eam_Maintain_id " +
					" left join Eam_Facilities sb on sbxq.Eam_Facilities_Id = sb.Eam_Facilities_Id " +
					" where sb.code= ? ";

			List<EamMaintainItem> maintainids = (List<EamMaintainItem>) OBDal.getInstance().getSession().createSQLQuery(sql).addEntity(EamMaintainItem.ENTITY_NAME).setString(0, code).list();    //保养项目ID

			EamMaintainPlanItems planDetails = null;
			EamMaintainPlanFacs maintainitem = null;
			//存保养计划
			EamMaintainPlan maintain = new EamMaintainPlan(); //保养计划

			if (maintainids.size() != 0) {
				if (orgIds != null) {
					if (usernames != null) {

						if (flag) {
							maintain.setStatus(status);
							maintain.setName(planName);
							maintain.setPlanStateTime(planstatetime);
							maintain.setPlanEndTime(planendtime);
							maintain.setLengthTime(sumtimes);
							maintain.setDistinguish(types);
							maintain.setCode(plancode);
							maintain.setPrewarningTime(Advancedays);
							maintain.setUser(userids);
							maintain.setOrganization(orgIds);
							maintain.setActive(effective);
							maintain.setPlanType(plantype);
							OBDal.getInstance().save(maintain);
							maintainid = maintain.getId();        //保养计划的ID
							OBDal.getInstance().flush();                //结束会话好获取到保养计划的ID
							flag = false;
						}

						//---------------------------------------------------------------------------------存保养设备-------------------------------------------------------------------------------------------
						String qql = "select * from Eam_Maintain_Plan where Eam_Maintain_Plan_id = ? ";

						//查询新创建的保养计划的ID
						EamMaintainPlan planids = (EamMaintainPlan) OBDal.getInstance().getSession().createSQLQuery(qql).addEntity(EamMaintainPlan.ENTITY_NAME).setString(0, maintainid).uniqueResult();

						String aql = "select * from Eam_Facilities where code = ? ";

						EamFacilities FacilitieIds = (EamFacilities) OBDal.getInstance().getSession().createSQLQuery(aql).addEntity(EamFacilities.ENTITY_NAME).setString(0, code).uniqueResult();    //设备ID

						String rql = "select * from emes_process"
								+ " where emes_process_id = (select emes_process_id from emes_process_facilities  "
								+ " where Eam_Facilities_Id = (select Eam_Facilities_Id from Eam_Facilities  where code = ?)) ";

						EmesProcess processs = (EmesProcess) OBDal.getInstance().getSession().createSQLQuery(rql).addEntity(EmesProcess.ENTITY_NAME).setString(0, code).uniqueResult(); //查询设备对应的工序

						if (FacilitieIds != null) {
							if (processs != null) {

								//查询已导入的保养计划下的设备
								String gql = "select * from eam_maintain_plan_facs where Eam_Facilities_Id = '" + FacilitieIds.getId() + "' and  Eam_Maintain_plan_id = '" + maintainid + "'";

								EamMaintainPlanFacs equipments = (EamMaintainPlanFacs) OBDal.getInstance().getSession().createSQLQuery(gql).addEntity(EamMaintainPlanFacs.ENTITY_NAME).uniqueResult();

								if (equipments == null) {
									//存保养计划下的设备
									maintainitem = new EamMaintainPlanFacs(); //保养计划下设备

									maintainitem.setEamMaintainPlan(planids);
									maintainitem.setEamFacilities(FacilitieIds);
									maintainitem.setUser(FacilitieIds.getUser());
									maintainitem.setStatus(status);
									maintainitem.setActive(effective);
									maintainitem.setEmesProcess(processs);
									OBDal.getInstance().save(maintainitem);
									itemid = maintainitem.getId();
									OBDal.getInstance().flush();
								}

								//------------------------------------------------------------------------------------存保养项目-----------------------------------------------------------------------------------------
								String eql = "select * from Eam_Maintain_Plan_Facs where Eam_Maintain_Plan_Facs_id = ? ";

								EamMaintainPlanFacs itmeids = (EamMaintainPlanFacs) OBDal.getInstance().getSession().createSQLQuery(eql).addEntity(EamMaintainPlanFacs.ENTITY_NAME).setString(0, itemid).uniqueResult();
								boolean output = true;    //判断保养项目是否已经配置好
								boolean results = true; //判断责任人是否存在
								planDetails = new EamMaintainPlanItems(); //保养计划下保养项目

								String hql = " select * from Eam_Maintain where maintain_code = ? ";

								EamMaintainItem mainnumbers = (EamMaintainItem) OBDal.getInstance().getSession().createSQLQuery(hql).addEntity(EamMaintainItem.ENTITY_NAME).setString(0, program).uniqueResult(); //查询保养项目的ID

								if (maintainids.contains(mainnumbers)) {

								} else {
									output = false;
								}

								if (output) {
									for (int o = 0; o < number; o++) {
										String oql = "select * from ad_user where name = ? ";

										User groupuers = (User) OBDal.getInstance().getSession().createSQLQuery(oql).addEntity(User.ENTITY_NAME).setString(0, users[o]).uniqueResult();    //查询计划负责人是否存在

										if (groupuers == null) {
											results = false;
										}
									}
									if (results) {

										//查询该计划已导入的设备下的保养项目
										String mql = "select Eam_Maintain_id from Eam_Maintain_Plan_Items where Eam_Maintain_Plan_Facs_id = ? ";

										List<String> xiangmus = (List<String>) OBDal.getInstance().getSession().createSQLQuery(mql).setString(0, itemid).list();

										if (xiangmus.contains(mainnumbers.getId())) {
											meg = "【第" + (row.getRowNum() + 1) + "行,设备编码为" + code + "的设备" + mainnumbers.getCode() + "保养项目重复！！";
										} else {
											planDetails.setEamMaintainPlanFacs(itmeids);
											planDetails.setEamMaintainItem(mainnumbers);
											planDetails.setType(types);
											planDetails.setStatus(status);
											planDetails.setPlanUser(username);
											planDetails.setGroupUser(groupusers);
											planDetails.setActive(effective);
											OBDal.getInstance().save(planDetails);
											OBDal.getInstance().flush();
										}
									} else {
										meg = "【第" + (row.getRowNum() + 1) + "行,小组人员不存在";
									}
								} else {
									meg = "【第" + (row.getRowNum() + 1) + "行,设备编码为" + code + "的设备下无" + program + "保养项目！！";
								}
							} else {
								meg = "【第" + (row.getRowNum() + 1) + "行,设备编码为" + code + "的设备无对应的工序！！";
							}
						} else {
							meg = "【第" + (row.getRowNum() + 1) + "行,设备编码为" + code + "的设备不存在！！";
						}
					} else {
						meg = "【第" + (row.getRowNum() + 1) + "行,计划负责人不存在！！";
					}
				} else {
					meg = "【第" + (row.getRowNum() + 1) + "行,组织机构不存在！！";
				}
			} else {
				meg = "【第" + (row.getRowNum() + 1) + "行,设备编码为" + code + "的设备下无保养项目！！";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return meg;
	}

	/**
	 * 读取单元格数据
	 */
	protected Object readValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		Double cellValue = null;
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			}
			cellValue = cell.getNumericCellValue();
			return cellValue.longValue() < cellValue ? cellValue : cellValue.longValue();
		} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return cell.getStringCellValue().trim();
		} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
			throw new RuntimeException("第" + (cell.getRowIndex() + 1) + "行，" + (cell.getColumnIndex() + 1) + "列，数据格式有问题,请检查");
		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else {
			return "";
		}
	}
}